﻿


using System;
using System.Data.OleDb;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Collections;


namespace EnglishWordsAnalyster
{
    /// <summary>
    /// 基础实体类
    /// </summary>
    public class BaseEntitys
    {
        ~BaseEntitys()
        {
            conn.Close();    
        }
        internal static bool ExcuteSQL(List<string> insertSqls)
        {
            try
            {
                foreach (string item in insertSqls)
                {
                    ExcuteSQL(item);
                }
                return true;
            }
            catch 
            {

                return false;
            }

        }

        public static bool ExcuteSQL(string SQL)
        {
            try
            {
                OleDbConnection conn = GetConnection();

                //conn.Open();

                OleDbCommand cmd = new OleDbCommand(SQL,conn);

               // cmd.CommandText = SQL;

                cmd.ExecuteNonQuery();

                return true;

            }
            catch 
            {
                return false;
            }
            finally
            {
                //conn.Close();
            }
        }

        private static  OleDbConnection conn;

        private static OleDbConnection GetConnection()
        {
            if (conn==null)
            {
                conn = new OleDbConnection(GetConnStr());
                conn.Open();
            }

            return conn;
        }

        private static string GetConnStr()
        {

           return ConfigurationManager.AppSettings["connectstring"];

        }

        public static DataTable QueryToDataTable(string SQL)
        {
            try
            {
                OleDbConnection conn = GetConnection();

                //conn.Open();

                OleDbDataAdapter da=new OleDbDataAdapter(SQL, conn);

                DataTable dt=new DataTable();
                
                da.Fill(dt);


                return dt;

            }
            catch
            {
                return null;
            }
            finally
            {
                //conn.Close();
            }
        }




        /// <summary>
        /// 获得当前月份的汉字表示（一、二、...十二）
        /// </summary>
        /// <returns></returns>
        public static string GetChineseMonthNum(string MounthNum)
        {
            string MounthChinese = "一";

            switch (MounthNum)
            {
                case "01": MounthChinese = "一";
                    break;
                case "02": MounthChinese = "二";
                    break;
                case "03": MounthChinese = "三";
                    break;
                case "04": MounthChinese = "四";
                    break;
                case "05": MounthChinese = "五";
                    break;
                case "06": MounthChinese = "六";
                    break;
                case "07": MounthChinese = "七";
                    break;
                case "08": MounthChinese = "八";
                    break;
                case "09": MounthChinese = "九";
                    break;
                case "10": MounthChinese = "十";
                    break;
                case "11": MounthChinese = "十一";
                    break;
                case "12": MounthChinese = "十二";
                    break;
                default: MounthChinese = "一";
                    break;
            }
            return MounthChinese;
        }




        /// <summary>
        /// 将List(string)中的每个字符串以指定的分隔符合并为一个字符串
        /// </summary>
        /// <param name="strList">要转换的List(string)</param>
        /// <param name="splitToAdd">指定的分隔符</param>
        /// <returns></returns>
        public static string ListToString(List<string> strList, string splitToAdd)
        {
            return ListToString(strList, splitToAdd, "", "");
        }

        /// <summary>
        /// 将List(string)中的每个字符串以指定的分隔符合并为一个字符串
        /// </summary>
        /// <param name="strList">要转换的List(string)</param>
        /// <param name="splitToAdd">指定的分隔符 1,1</param>
        /// <param name="head">每个元素的前置字符 :如 '1'</param>
        /// <param name="foot">每个元素的后置字符:如'1'</param>
        /// <returns></returns>
        public static string ListToString(List<string> strList, string splitToAdd, string head, string foot)
        {
            if (strList == null || strList.Count <= 0) return string.Empty;
            if (string.IsNullOrEmpty(splitToAdd)) splitToAdd = "";
            if (string.IsNullOrEmpty(head)) head = "";
            if (string.IsNullOrEmpty(foot)) foot = "";

            string Result = "";

            for (int i = 0; i < strList.Count; i++)
            {
                string perStr = strList[i];

                if (!string.IsNullOrEmpty(perStr))
                {
                    Result += head + perStr + foot;

                    if (i != strList.Count - 1) Result += splitToAdd;
                }
            }
            return Result;
        }





        /// <summary>
        /// 获得数据表中的指定列名的值列表
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="ColumnName">指定列名</param>
        /// <returns></returns>
        public static List<object> GetColumnValueList(DataTable dt, string ColumnName)
        {

            List<object> valueList = new List<object>();

            if (dt == null) return valueList;
            if (!dt.Columns.Contains(ColumnName)) throw new Exception("数据表中不包含指定的列名[" + ColumnName + "]");

            foreach (DataRow dr in dt.Rows)
            {
                if (!valueList.Contains(dr[ColumnName])) valueList.Add(dr[ColumnName]);
            }
            return valueList;
        }

        /// <summary>
        /// 获得数据表中的指定列名的值列表
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="ColumnName">指定列名</param>
        /// <returns></returns>
        public static List<string> GetColumnValueListString(DataTable dt, string ColumnName)
        {

            List<string> valueList = new List<string>();

            if (dt == null) return valueList;
            if (!dt.Columns.Contains(ColumnName)) throw new Exception("数据表中不包含指定的列名[" + ColumnName + "]");

            foreach (DataRow dr in dt.Rows)
            {
                if (dr[ColumnName] is DBNull) continue;
                if (!valueList.Contains(dr[ColumnName].ToString())) valueList.Add(dr[ColumnName].ToString());
            }
            return valueList;
        }











 


        #region 根据字段名从数据行中取值

        static public string GetValueString(string ColumnName, DataRow dr)
        {
            try
            {
                return dr[ColumnName].ToString();
            }
            catch
            {
                return "";
            }
        }

        static public int GetValueInt(string ColumnName, DataRow dr)
        {
            try
            {
                return int.Parse(dr[ColumnName].ToString());
            }
            catch
            {
                return 0;
            }
        }

        static public decimal GetValueDecimal(string ColumnName, DataRow dr)
        {
            try
            {
                return decimal.Parse(dr[ColumnName].ToString());
            }
            catch
            {
                return 0;
            }
        }

        static public DateTime GetValueDateTime(string ColumnName, DataRow dr)
        {
            try
            {
                return DateTime.Parse(dr[ColumnName].ToString());
            }
            catch
            {
                return DateTime.MinValue;
            }
        }

        #endregion

        public static string ExceptionNotify(Exception e)
        {
            return e.Message + "\r\n" + "----------------------------\r\n" + e.StackTrace;
        }


        /// <summary>
        /// 字符串长度是否合法（汉字算两个长度）
        /// </summary>
        /// <param name="SourceString">原字符串</param>
        /// <param name="MaxLength">最大长度</param>
        /// <returns></returns>
        public static bool IsValidStringLength(string SourceString, int MaxLength)
        {
            int ilength = System.Text.Encoding.Default.GetBytes(SourceString.Trim()).Length;

            if (ilength <= MaxLength) return true;

            return false;
        }







        /// <summary>
        /// 查询单值
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        internal static object QuerySingleValue(string SQL)
        {
            try
            {
                return QuerySingleValue(SQL, 0);

            }
            catch
            {
                return null;
            }


        }
        /// <summary>
        /// 查询单值
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        internal static object QuerySingleValue(string SQL, string ColumnName)
        {
            try
            {
                DataRow dr = QuerySingleRow(SQL);

                return dr[ColumnName];

            }
            catch
            {
                return null;
            }


        }
        /// <summary>
        /// 查询单值
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        internal static object QuerySingleValue(string SQL, int ColumnIndex)
        {
            try
            {
                DataRow dr = QuerySingleRow(SQL);

                return dr[ColumnIndex];

            }
            catch
            {
                return null;
            }


        }


        /// <summary>
        /// 查询单值
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        internal static DataRow QuerySingleRow(string SQL)
        {
            DataTable dt = QueryToDataTable(SQL);

            if (dt == null || dt.Rows.Count < 1) return null;

            return dt.Rows[0];
        }

        internal static List<object> QuerySingleColumn(string SQL)
        {
            return QuerySingleColumn(SQL, 0);
        }

        internal static List<object> QuerySingleColumn(string SQL, int ColumnNumber)
        {
            DataTable dt = QueryToDataTable(SQL);

            if (dt == null || dt.Rows.Count < 1) return null;

            List<object> columnValues = new List<object>();

            foreach (DataRow dr in dt.Rows)
            {
                columnValues.Add(dr[ColumnNumber]);
            }

            return columnValues;
        }

        internal static List<object> QuerySingleColumn(string SQL, string ColumnName)
        {
            DataTable dt = QueryToDataTable(SQL);

            if (dt == null || dt.Rows.Count < 1) return null;

            List<object> columnValues = new List<object>();

            foreach (DataRow dr in dt.Rows)
            {
                columnValues.Add(dr[ColumnName]);
            }

            return columnValues;
        }

        internal static List<string> QuerySingleColumnStrings(string SQL)
        {
            return QuerySingleColumnStrings(SQL, 0);
        }

        internal static List<string> QuerySingleColumnStrings(string SQL, int ColumnNumber)
        {

            List<object> values = QuerySingleColumn(SQL, ColumnNumber);

            if (values == null || values.Count < 1) return null;

            List<string> list = new List<string>();

            foreach (object var in values)
            {
                list.Add(var.ToString());
            }

            return list;
        }
        public static bool HasNotData(DataTable dt)
        {
            if (dt == null || dt.Rows.Count <= 0) return true;
            return false;
        }

        //public static bool HasNotData(IList list)
        //{
        //    if (list == null || list.Count <= 0) return true;
        //    return false;
        //}
        //public static bool HasNotData(IDictionary dc)
        //{
        //    if (dc == null || dc.Count <= 0) return true;
        //    return false;
        //}
        public static bool HasNotData(ICollection co)
        {
            if (co == null || co.Count <= 0) return true;
            return false;
        }





        /// <summary>
        /// 处理逗号分隔字符串
        /// </summary>
        /// <param name="SourceStr"></param>
        /// <returns></returns>
        public static string TrimComma(string SourceStr)
        {
            if (string.IsNullOrEmpty(SourceStr)) return "";

            string targetStr = "";

            string[] subStrs = SourceStr.Trim().Split(","[0]);

            for (int i = 0; i < subStrs.Length; i++)
            {
                string perStr = subStrs[i].Trim();

                if (string.IsNullOrEmpty(perStr)) continue;

                targetStr += "," + perStr;
            }

            if (string.IsNullOrEmpty(targetStr)) return "";

            targetStr = targetStr.Substring(1, targetStr.Length - 1);

            return targetStr;
        }

        /// <summary>
        /// 处理逗号分隔字符串集合
        /// </summary>
        /// <param name="IC"></param>
        /// <returns></returns>
        public static string GetSplitString(ICollection<string> IC)
        {
            if (IC == null) return "";

            string sourceStr = "";
            foreach (string str in IC)
            {
                if (string.IsNullOrEmpty(str)) continue;
                sourceStr += "," + str;
            }
            return TrimComma(sourceStr);


        }


        /// <summary>
        /// 获得指定日期的当月第一天
        /// </summary>
        /// <param name="theDay"></param>
        /// <returns></returns>
        public static DateTime GetMonthFirstDate(DateTime theDay)
        {
            return new DateTime(theDay.Year, theDay.Month, 1);
        }
        /// <summary>
        /// 获得指定日期的当月最后一天
        /// </summary>
        /// <param name="theDay"></param>
        /// <returns></returns>
        public static DateTime GetMonthLastDate(DateTime theDay)
        {
            return new DateTime(theDay.Year, theDay.Month, DateTime.DaysInMonth(theDay.Year, theDay.Month));
        }



        /// <summary>
        /// 获得当前月份的的第一天
        /// </summary>
        /// <returns></returns>
        public static DateTime GetCurrentMonthFirstDate()
        {
            return new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

        }

        /// <summary>
        /// 获得当前月份的最后一天
        /// </summary>
        /// <returns></returns>
        public static DateTime GetCurrentMonthLastDate()
        {
            return new DateTime(DateTime.Today.Year, DateTime.Today.Month, DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.Month));
        }


    }
}

